How to use the Filter functions of TClientdatasets for dates? |
您所在的位置：网站首页 › delphi tclientdataset › How to use the Filter functions of TClientdatasets for dates? |
|
If you want to use a Filter expression instead of an OnFilterRecord handler, it is worthwhile taking a look at the source of the TExprParser class, which is what TClientDataSet uses for textual filters. It is contained in the DBCommon.Pas unit file in your Delphi source. The D7 TExprParser supports the following functions: function TExprParser.TokenSymbolIsFunc(const S: string) : Boolean; begin Result := (CompareText(S, 'UPPER') = 0) or (CompareText(S, 'LOWER') = 0) or [...] (CompareText(S, 'YEAR') = 0) or (CompareText(S, 'MONTH') = 0) or (CompareText(S, 'DAY') = 0) or [...] end;Btw, it is worthwhile looking through the rest of TExprParser's source because it reveals things like support for the IN construct found in SQL. On my (UK) system, dates display in a DBGrid as dd/mm/yyyy. Given that, all of the filter expressions shown below work in D7 without producing an exception and return the expected results: procedure TForm1.Button1Click(Sender: TObject); begin // ADate field of CDS is initialised by // CDS1.FieldByName('ADate').AsDateTime := Now - random(365); edFilter.Text := 'ADate = ''10/2/2017'''; // works, date format = dd/mm/yyyy edFilter.Text := 'Month(ADate) = 2'; // works edFilter.Text := 'Year(ADate) = 2017'; // works edFilter.Text := '(Day(ADate) = 10) and (Year(ADate) = 2017)'; // works CDS1.Filtered := False; CDS1.Filter := edFilter.Text; CDS1.Filtered := True; end;If you don't get similar results, I'd suggest you start by looking at your regional settings and how dates are displayed in a TDBGrid. Filter expressions are not particularly efficient compared to the alternative method of filtering, namely to use the OnFilterRecord event. In the event handler, you can use e.g. DecodeDateTime to decode it into its Year, Month, Day, etc components and apply whatever tests you like to their values. Then set Accept to True or False. Update I gather from your comment to an answer here Delphi: check if Record of DataSet is visible or filtered that the problem you had with this was that the date functions supported by TExprParser.TokenSymbolIsFunc() are not in your user's language. You can use the code below to translate the date function names in the filter expression. See the embedded comments for explanation of how it works type TForm1 = class(TForm) [...] public NameLookUp : TStringList; [...] end; procedure TForm1.FormCreate(Sender: TObject); begin NameLookUp := TStringList.Create; // Assume Y, M & C are the local-language names NameLookUp.Add('Y=Year'); NameLookUp.Add('M=Month'); NameLookUp.Add('D=Day'); [...] end; procedure TForm1.Log(const Title, Msg : String); begin Memo1.Lines.Add(Title + ' : ' + Msg); end; function TForm1.TranslateExpression(const Input : String; ADataSet : TDataSet) : String; var SS : TStringStream; TokenText : String; LookUpText : String; Parser : TParser; CH : Char; begin SS := TStringStream.Create(Input); Parser := TParser.Create(SS); Result := ''; try CH := Parser.Token; // following translates Input by parsing it using TParser from Classes.Pas while Parser.Token #0 do begin TokenText := Parser.TokenString; case CH of toSymbol : begin // The following will translate TokenText for symbols // but only if TokenText is not a FieldName of ADataSet if ADataSet.FindField(TokenText) = Nil then begin LookUpText := NameLookUp.Values[TokenText]; if LookUpText '' then Result := Result + LookUpText else Result := Result + TokenText; end else Result := Result + TokenText; end; toString : // SingleQuotes surrounding TokenText in Input and ones embedded in it // will have been stripped, so reinstate the surrounding ones and // double-up the embedded ones Result := Result + '''' + StringReplace(TokenText, '''', '''''', [rfReplaceAll]) + ''''; else Result := Result + TokenText; end; { case } if Result '' then Result := Result + ' '; CH := Parser.NextToken; end; finally Parser.Free; SS.Free; end; Log('TransResult', Result); end; procedure TForm1.btnSetFilterExprClick(Sender: TObject); begin // Following tested with e.g edFilter.Text = // LastName = 'aaa' and Y(BirthDate) = 2000 UpdateFilter2; end; procedure TForm1.UpdateFilter2; var T1 : Integer; begin CDS1.OnFilterRecord := Nil; T1 := GetTickCount; CDS1.DisableControls; try CDS1.Filtered := False; CDS1.Filter := TranslateExpression(edFilter.Text, CDS1); if CDS1.Filter '' then begin CDS1.Filtered := True; end; Log('Filter update time', IntToStr(GetTickCount - T1) + 'ms'); finally CDS1.EnableControls; end; end; |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |